﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.library;
using System.ComponentModel.Composition;
using System.Data;
using com.nm;
using com.nm.Helper;
using com.nm.library.Excels;
using com.nm.library.Helper;
using DistributeAPI.Model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;

namespace DistributeAPI.Export
{
    /// <summary>
    /// 企划订单量已计算数据Excel导入
    /// </summary>
    [Export(typeof(ExcelImport))]
    public class PlanningOrderQuantity : ExcelImport
    {
        private static int num = 0;
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        public override ExcelImportType Type => ExcelImportType.PLANORDERQUANTITY;

        public override Dictionary<string, ImportVerify> DictFields => dictFields;

        /// <summary>
        /// Excel字段映射
        /// </summary>
        private static readonly Dictionary<string, ImportVerify> dictFields = new List<ImportVerify> {
            new ImportVerify{ ColumnName="店铺编号",    FieldName="ShopCode" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="店铺名称",    FieldName="ShopName" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,false,false)},
            new ImportVerify{ ColumnName="货号",    FieldName="MaterialID" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="尺码名称",    FieldName="SizeName" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,10,true,false)},
            new ImportVerify{ ColumnName="尺码长度",    FieldName="SizeLength" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,100,true,false)},
            new ImportVerify{ ColumnName="数量",    FieldName="Qty" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,10,true,false)},
           }.ToDictionary(e => e.ColumnName, e => e);

        public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, int userId)
        {
            int errorIndex = 0;
            var index = 0;
            var existRowCount = 0;
            #region 构造一个新的Datatable dtError

            //构造一个新的Datatable dtError
            DataTable dtError = new DataTable();
            dtError.Columns.Add("CompanyID", System.Type.GetType("System.String"));
            dtError.Columns.Add("ShopID", System.Type.GetType("System.String"));
            dtError.Columns.Add("ShopCode", System.Type.GetType("System.String"));
            dtError.Columns.Add("ShopName", System.Type.GetType("System.String"));
            dtError.Columns.Add("MaterialID", System.Type.GetType("System.String"));
            dtError.Columns.Add("SizeID", System.Type.GetType("System.String"));
            dtError.Columns.Add("SizeName", System.Type.GetType("System.String"));
            dtError.Columns.Add("SizeLength", System.Type.GetType("System.String"));
            dtError.Columns.Add("SizeMinQty", System.Type.GetType("System.Int32"));
            dtError.Columns.Add("Qty", System.Type.GetType("System.Int32"));
            dtError.Columns.Add("Flag", System.Type.GetType("System.Int32"));
            dtError.Columns.Add("MofifyDTM", System.Type.GetType("System.DateTime"));

            #endregion 构造一个新的Datatable dtError
            //删除语句集合
            HashSet<string> deleteHashSet = new HashSet<string>();
            var result = new ImportResult();
            try
            {
                //先遍历所有的数据.
                //拼接删除语句放到一个list
                var cmdList = new List<CommandInfo>();
                PlanningOrderQuantityModelSizeList model = new PlanningOrderQuantityModelSizeList();
                model.Checker = "Excel2";
                //var uid = Guid.NewGuid().ToString().Replace("-", "");
                // var tempTableName = $" goods.goods_{uid} ";

                #region 构造一个新的Datatable dtNew

                //构造一个新的Datatable dtNew
                DataTable dtNew = new DataTable();
                dtNew.Columns.Add("ExtrSource", System.Type.GetType("System.String"));
                dtNew.Columns.Add("CompanyID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("ShopID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("ShopCode", System.Type.GetType("System.String"));
                dtNew.Columns.Add("MaterialID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("SizeID", System.Type.GetType("System.String"));
                dtNew.Columns.Add("SizeLength", System.Type.GetType("System.String"));
                dtNew.Columns.Add("SizeMinQty", System.Type.GetType("System.Int32"));
                dtNew.Columns.Add("Qty", System.Type.GetType("System.Int32"));
                dtNew.Columns.Add("TotalQty", System.Type.GetType("System.Int32"));
                dtNew.Columns.Add("Flag", System.Type.GetType("System.Int32"));
                dtNew.Columns.Add("MofifyDTM", System.Type.GetType("System.DateTime"));
                dtNew.Columns.Add("Checker", System.Type.GetType("System.String"));

                #endregion 构造一个新的Datatable dtNew



                string shopCode = string.Empty;
                string materialId = string.Empty;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    errorIndex = i + 7;
                    model.ShopCode = dt.Rows[i]["ShopCode"].ToString().Trim();
                    model.MaterialID = dt.Rows[i]["MaterialID"].ToString().Trim();
                    if (dt.Rows[i]["SizeLength"] != DBNull.Value)
                    {
                        model.SizeLength = dt.Rows[i]["SizeLength"].ToString().Trim();
                    }
                    else
                    {
                        InsertToError(dtError, model);
                        continue;
                    }
                    if (model.ShopCode.Length > 0 &&
                        model.MaterialID.Length > 0)
                    {
                        //检测是否同一店铺,同一货号有多条记录,只保留最后的一条
                        var rowExist = dtNew.Select($"ShopCode ='{model.ShopCode}' and MaterialID = '{model.MaterialID}' and SizeLength <> '{model.SizeLength}' ");
                        if (rowExist != null && rowExist.Count() > 0)
                        {
                            foreach (var r in rowExist)
                            {
                                dtNew.Rows.Remove(r);
                                existRowCount++;
                                index--;
                            }

                        }
                    }
                    else
                    {
                        InsertToError(dtError, model);
                        continue;
                    }

                    //数量
                    int Qty = 0;
                    if (dt.Rows[i]["Qty"] != DBNull.Value)
                    {
                        int temp = 0;
                        Int32.TryParse(dt.Rows[i]["Qty"]?.ToString().Trim(), out temp);
                        Qty = temp;
                        model.Qty = Qty;
                    }
                    else
                    {
                        InsertToError(dtError, model);
                        continue;
                    }



                    //验证尺码名称是否存在
                    string sizename = string.Empty;
                    if (dt.Rows[i]["SizeName"] != DBNull.Value)
                    {
                        sizename = dt.Rows[i]["SizeName"].ToString().Trim();
                        //替换2XL为XXL
                        if (sizename == "2XL")
                        {
                            sizename = "XXL";
                        }
                        model.SizeName = sizename;
                    }


                    //查找店铺是否存在
                    string strSqlShop = $@"
SELECT shopid,shopname from 
ndw.ndw_Shop
where ShopCode='{model.ShopCode}'	and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'
";
                    var dtShop = Helper.ExecuteDataTable(new VerticaCommand(strSqlShop, null));
                    if (dtShop.Rows.Count > 0)
                    {
                        model.ShopID = dtShop.Rows[0]["ShopId"]?.ToString();
                        model.ShopName = dtShop.Rows[0]["ShopName"]?.ToString();
                    }
                    else
                    {
                        InsertToError(dtError, model);
                        continue;
                    }

                    string strSqlSizeName = $@"
 SELECT stt.SizeName from 
  nods.nods_erp_SD_Mat_Material2 c left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and 	sst.ExtrSource=c.ExtrSource and sst.CompanyID=c.CompanyID 
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and 	sst.ExtrSource=stt.ExtrSource and sst.CompanyID=stt.CompanyID
	 WHERE 
  	 c.ExtrSource='{UserInfo.UserExtrSource}' and c.CompanyID='{UserInfo.UserCompanyId}' and  c.MaterialID='{model.MaterialID}'
  and stt.SizeName = '{sizename}'
";
                    var dtSizeName = Helper.ExecuteDataTable(new VerticaCommand(strSqlSizeName, null));


                    //获取根据尺码名称获取尺码内码

                    if (!string.IsNullOrWhiteSpace(sizename))
                    {
                        string strSql = $@"
                                                        select SizeID from nods.nods_erp_SD_Mat_Size
                                                        WHERE 
                                                         ExtrSource='{UserInfo.UserExtrSource}' and 
                                                        AllowUsed = 1 and 
                                                        SizeName = '{sizename}' limit 1
                                                        ";
                        var cmd = Helper.GetSqlStringCommand(strSql);
                        var dtSizeId = Helper.ExecuteDataTable(cmd);
                        if (dtSizeId != null && dtSizeId.Rows.Count > 0)
                        {
                            model.SizeID = dtSizeId.Rows[0]["SizeID"].ToString();
                        }
                    }

                    #region Delete

                    if (i > 0)
                    {
                        shopCode = dt.Rows[i - 1]["ShopCode"].ToString().Trim();
                        materialId = dt.Rows[i - 1]["materialId"].ToString().Trim();
                        if (shopCode != model.ShopCode || materialId != model.MaterialID)
                        {
                            string deleteSql = $@"
                                            Delete from
                                            ngoods.ngoods_DC_Bas_PLOrder
                                            where
                                            shopcode='{model.ShopCode}' 
                                            and MaterialID='{model.MaterialID}' 
	                                        and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'
                                            ";
                            cmdList.Add(new CommandInfo()
                            {
                                CommandText = deleteSql
                            });
                        }
                    }
                    else
                    {
                        string deleteSql = $@"
                                            Delete from
                                            ngoods.ngoods_DC_Bas_PLOrder
                                            where
                                            shopcode='{model.ShopCode}' 
                                            and MaterialID='{model.MaterialID}' 
                                            and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'
                                            ";
                        deleteHashSet.Add(deleteSql);
                    }

                    #endregion Delete
                    if (dtSizeName.Rows.Count == 0)
                    {
                        if (dt.Rows[i]["SizeName"] != DBNull.Value)
                        {
                            sizename = dt.Rows[i]["SizeName"].ToString().Trim();
                        }
                        model.SizeName = sizename;
                        InsertToError(dtError, model);
                        continue;
                    }

                    DataRow dr = dtNew.NewRow();
                    dr["ExtrSource"] = UserInfo.UserExtrSource;
                    dr["CompanyID"] = UserInfo.UserCompanyId;
                    dr["ShopID"] = model.ShopID;
                    dr["ShopCode"] = model.ShopCode;
                    dr["MaterialID"] = model.MaterialID;
                    dr["SizeID"] = model.SizeID;
                    dr["SizeMinQty"] = model.SizeMinQty;
                    dr["Qty"] = model.Qty;
                    dr["TotalQty"] = model.TotalQty;
                    dr["SizeLength"] = model.SizeLength;
                    dr["Flag"] = 1;
                    dr["MofifyDTM"] = model.MofifyDTM;
                    dr["Checker"] = model.Checker;
                    dtNew.Rows.Add(dr);

                    index++;
                }
                //拼接删除的sql语句
                foreach (var deleteSql in deleteHashSet)
                {
                    cmdList.Add(new CommandInfo()
                    {
                        CommandText = deleteSql
                    });
                }
                var list = ModelConvertHelper<PlanningOrderQuantityModelSizeList>.ConvertToModel(dtNew);
                var group = list.GroupBy(p => new { p.ShopCode, p.MaterialID });
                foreach (var item in group)
                {
                    foreach (var m in item)
                    {
                        m.TotalQty = item.Sum(p => p.Qty);
                    }
                }
                DataTable dtInsert = DataTableHelper.LinqToDataTable(list);
                dtInsert.Columns.Remove("SizeName");
                dtInsert.Columns.Remove("ShopName");
                dtInsert.Columns.Remove("OldMaterialID");
                dtInsert.Columns.Remove("OldSizeLength");
                dtInsert.Columns.Remove("SizeCodeType");
                //然后调用批量插入的方法
                Helper.ExecuteSqlTran(cmdList, dtInsert, "ngoods.ngoods_DC_Bas_PLOrder", 1000);
                if (dtError.Rows.Count > 0)
                {
                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    result.IsSuccess = true;
                    result.ExtraInfo = "导入成功";
                    result.Message = $"成功导入{index}条数据,错误{dt.Rows.Count - index - existRowCount}条";
                    result.Url = ExcelUtil.ExportErrorExeclToDB(dtError, path, dictFields, "下载错误数据", num);
                    num++;
                }
                else
                {
                    result.IsSuccess = true;
                    result.ExtraInfo = "导入成功";
                    result.Message = $"成功导入{index}条数据,错误{dt.Rows.Count - index - existRowCount}条";
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                if (dtError.Rows.Count > 0)
                {
                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    result.IsSuccess = false;
                    result.ExtraInfo = "导入失败";
                    result.Message = $"成功导入{index}条数据,错误{dt.Rows.Count - index - existRowCount}条";
                    result.Url = ExcelUtil.ExportErrorExeclToDB(dtError, path, dictFields, "下载错误数据", num);
                    num++;
                    return result;
                }
            }
            return result;
        }

        public override DataTable GetExportData(string Keys)
        {
            throw new NotImplementedException();
        }

        public void InsertToError(DataTable dtError, PlanningOrderQuantityModelSizeList model)
        {
            DataRow dr = dtError.NewRow();
            dr["CompanyID"] = model.CompanyID;
            dr["ShopName"] = model.ShopName;
            dr["ShopCode"] = model.ShopCode;
            dr["MaterialID"] = model.MaterialID;
            dr["SizeID"] = model.SizeID;
            dr["SizeName"] = model.SizeName;
            dr["SizeLength"] = model.SizeLength;
            dr["SizeMinQty"] = model.SizeMinQty;
            dr["Qty"] = model.Qty;
            dr["Flag"] = 1;
            dr["MofifyDTM"] = model.MofifyDTM;
            dtError.Rows.Add(dr);
        }

    }
}
